﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.ComponentModel;
using System.Windows.Forms;
using System.Runtime.InteropServices;

namespace KnowledgeAssistant
{
    static class DatabaseConnect
    {
        public static Boolean crypt = false;
        private static SqlConnection connection;
        private static string connectionString = "";
        public static string not_crypted = Environment.CurrentDirectory + "\\not_crypted.config";
        public static string crypted = Environment.CurrentDirectory + "\\crypted.config";
        public static string SecretKey = "qwerty77"; 

        #region Methods

        public static void Open()
        {
            if (InitConnString())
            {
                try
                {
                    connection = new SqlConnection(connectionString);
                    connection.Open();
                }
                catch 
                {
                    MessageBox.Show("SQL-соединение не может быть установлено! \n \nВозможные причины: \n  сервер БД не в сети; \n  неверно указана сетевая ссылка. \n ");
                    System.Diagnostics.Process.GetCurrentProcess().Kill();
                }
            }
            else
            {                
                System.Diagnostics.Process.GetCurrentProcess().Kill();
            }
        }

        private static Boolean CheckExistFile(string path)
        {
            if (System.IO.File.Exists(not_crypted))
            {
                StreamReader sr = new StreamReader(not_crypted);
                string line = null;
                int count_processed_lines = 0;
                Boolean success_readed = false;
                while (!sr.EndOfStream && !success_readed)
                {
                    line = sr.ReadLine(); count_processed_lines++;

                    if (line != "" && line.Contains("<connectionString>") && line.Contains("</connectionString>"))
                    {
                        connectionString = StudentsForm.parse_XmlTag(line, "connectionString").Trim();
                        line = sr.ReadLine(); count_processed_lines++;
                        success_readed = true;
                    }
                    else
                        if (line != "" && (line.Contains("<connectionString>") || line.Contains("</connectionString>")))
                            MessageBox.Show("Не удалось распознать директиву для SQL-соединения! Строка с ошибкой: " + count_processed_lines);
                }
                sr.Close();
                sr.Dispose();
                if (success_readed) { return true; } else return false;
            }
            else return false;
        }

        private static Boolean InitConnString()
        {
            if (CheckExistFile(not_crypted))
            {
                return true;
            }
            else
            {
                if (System.IO.File.Exists(crypted))
                {
                    GCHandle gch = GCHandle.Alloc(crypted, GCHandleType.Pinned);
                    try
                    {
                        Crypto.DecryptFile(crypted, not_crypted, SecretKey);
                    }
                    catch (Exception)
                    {
                        MessageBox.Show("Не удалось расшифровать файл с директивой для SQL-соединения!");
                        return false;
                    }
                    gch.Free();
                    crypt = (CheckExistFile(not_crypted));
                    FileInfo file = new FileInfo(DatabaseConnect.not_crypted);
                    file.Delete();
                    return crypt;
                }
                else { MessageBox.Show("Не найден файл с директивой для SQL-соединения!"); return false; }
            }            
        }

        public static void Close()
        {
            connection.Close();
        }

        public static DataSet execQuery(string query)
        {
            DataSet data = new DataSet();
            SqlCommand com = new SqlCommand(query, connection);
            SqlDataAdapter sda = new SqlDataAdapter(com);
            sda.Fill(data);
            return data;
        }

        public static DataSet execSP(string sp_name, List<string> parameters)
        {
            DataSet data = new DataSet();

            string spparams = "";

            for (int i = 0; i < parameters.Count; i++)
            {
                spparams += "@param" + i + ',';
            }

            spparams = spparams.Remove(spparams.Length - 1);
            
            SqlCommand com = new SqlCommand("exec " + sp_name + " " + spparams, connection);

            for (int i = 0; i < parameters.Count; i++)
            {
                com.Parameters.AddWithValue("@param" + i, parameters[i]);
            }

            SqlDataAdapter sda = new SqlDataAdapter(com);
            sda.Fill(data);
         
            return data;
        }

        public static DataSet execSP(string sp_name, List<string> parameters, Bitmap image)
        {
            DataSet data = new DataSet();

            string spparams = "";

            for (int i = 0; i < parameters.Count+1; i++)
            {
                spparams += "@param" + i + ',';
            }

            spparams = spparams.Remove(spparams.Length - 1);

            SqlCommand com = new SqlCommand("exec " + sp_name + " " + spparams, connection);

            for (int i = 0; i < parameters.Count; i++)
            {
                com.Parameters.AddWithValue("@param" + i, parameters[i]);
            }

            MemoryStream ms = new MemoryStream();
            image.Save(ms,System.Drawing.Imaging.ImageFormat.Jpeg);
            ms.Close();
            com.Parameters.AddWithValue("@param" + (parameters.Count).ToString(), ms.ToArray());

            SqlDataAdapter sda = new SqlDataAdapter(com);
            sda.Fill(data);

            return data;
        }

        public static DataSet execReportSP(string sp_name, List<string> parameters)
        {
            DataSet data = new DataSet();

            string spparams = "";

            for (int i = 0; i < parameters.Count; i++)
            {
                spparams += parameters[i] + ',';
            }

            spparams = spparams.Remove(spparams.Length - 1);

            SqlCommand com = new SqlCommand("exec " + sp_name + " " + spparams, connection);

            SqlDataAdapter sda = new SqlDataAdapter(com);
            sda.Fill(data);

            return data;
        }

        public static void execSPNonQuery(string sp_name, List<string> parameters)
        {
            string spparams = "";

            for (int i = 0; i < parameters.Count; i++)
            {
                spparams += "@param" + i + ',';
            }

            spparams = spparams.Remove(spparams.Length - 1);

            SqlCommand com = new SqlCommand("exec " + sp_name + " " + spparams, connection);

            for (int i = 0; i < parameters.Count; i++)
            {
                com.Parameters.AddWithValue("@param" + i, parameters[i]);
            }

            com.ExecuteNonQuery();
            
        }

        public static DataSet execSP(string sp_name)
        {
            DataSet data = new DataSet();
            SqlCommand com = new SqlCommand("exec " + sp_name, connection);
            SqlDataAdapter sda = new SqlDataAdapter(com);
            sda.Fill(data);
            return data;
        }

        public static void execSP(string sp_name, DataTable table)
        {
            SqlCommand com = new SqlCommand("exec " + sp_name, connection);
            SqlDataAdapter sda = new SqlDataAdapter(com);
            sda.Fill(table);
        }

        #endregion


    }
}
